﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Eval;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;

namespace FamilyTree.BLL
{

    public class NPOIHelper
    {
      


        private static MemoryStream ExportDT(DataTable dtSource, string strHeaderText)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
            HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
            HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
            int[] arrColWidth = new int[dtSource.Columns.Count];
            foreach (DataColumn item in dtSource.Columns)
            {
                arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
            }
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }
            int rowIndex = 0;
            foreach (DataRow row in dtSource.Rows)
            {
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet() as HSSFSheet;
                    }
                    HSSFRow headerRow2 = sheet.CreateRow(0) as HSSFRow;
                    headerRow2.HeightInPoints = 25f;
                    headerRow2.CreateCell(0).SetCellValue(strHeaderText);
                    HSSFCellStyle headStyle2 = workbook.CreateCellStyle() as HSSFCellStyle;
                    headStyle2.Alignment = HorizontalAlignment.Center;
                    HSSFFont font2 = workbook.CreateFont() as HSSFFont;
                    font2.FontHeightInPoints = 20;
                    font2.Boldweight = 700;
                    headStyle2.SetFont(font2);
                    headerRow2.GetCell(0).CellStyle = headStyle2;
                    sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
                    HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow;
                    HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
                    headStyle.Alignment = HorizontalAlignment.Center;
                    HSSFFont font = workbook.CreateFont() as HSSFFont;
                    font.FontHeightInPoints = 10;
                    font.Boldweight = 700;
                    headStyle.SetFont(font);
                    foreach (DataColumn column2 in dtSource.Columns)
                    {
                        headerRow.CreateCell(column2.Ordinal).SetCellValue(column2.ColumnName);
                        headerRow.GetCell(column2.Ordinal).CellStyle = headStyle;
                        sheet.SetColumnWidth(column2.Ordinal, (arrColWidth[column2.Ordinal] + 1) * 256);
                    }
                    rowIndex = 2;
                }
                HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
                foreach (DataColumn column in dtSource.Columns)
                {
                    HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;
                    string drValue = row[column].ToString();
                    switch (column.DataType.ToString())
                    {
                        case "System.String":
                            {
                                if (isNumeric(drValue, out var result))
                                {
                                    double.TryParse(drValue, out result);
                                    newCell.SetCellValue(result);
                                }
                                else
                                {
                                    newCell.SetCellValue(drValue);
                                }
                                break;
                            }
                        case "System.DateTime":
                            {
                                DateTime.TryParse(drValue, out var dateV);
                                newCell.SetCellValue(dateV);
                                newCell.CellStyle = dateStyle;
                                break;
                            }
                        case "System.Boolean":
                            {
                                bool boolV = false;
                                bool.TryParse(drValue, out boolV);
                                newCell.SetCellValue(boolV);
                                break;
                            }
                        case "System.Int16":
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            {
                                int intV = 0;
                                int.TryParse(drValue, out intV);
                                newCell.SetCellValue(intV);
                                break;
                            }
                        case "System.Double":
                        case "System.Decimal":
                            {
                                double doubV = 0.0;
                                double.TryParse(drValue, out doubV);
                                newCell.SetCellValue(doubV);
                                break;
                            }
                        case "System.DBNull":
                            newCell.SetCellValue("");
                            break;
                        default:
                            newCell.SetCellValue("");
                            break;
                    }
                }
                rowIndex++;
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0L;
                return ms;
            }
        }

        private static void ExportDTI(DataTable dtSource, string strHeaderText, FileStream fs)
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet;
            XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle;
            XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat;
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
            int[] arrColWidth = new int[dtSource.Columns.Count];
            foreach (DataColumn item in dtSource.Columns)
            {
                arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
            }
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }
            int rowIndex = 0;
            foreach (DataRow row in dtSource.Rows)
            {
                if (rowIndex == 0)
                {
                    XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow;
                    XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
                    headStyle.Alignment = HorizontalAlignment.Center;
                    XSSFFont font = workbook.CreateFont() as XSSFFont;
                    font.FontHeightInPoints = 10;
                    font.Boldweight = 700;
                    headStyle.SetFont(font);
                    foreach (DataColumn column2 in dtSource.Columns)
                    {
                        headerRow.CreateCell(column2.Ordinal).SetCellValue(column2.ColumnName);
                        headerRow.GetCell(column2.Ordinal).CellStyle = headStyle;
                        sheet.SetColumnWidth(column2.Ordinal, (arrColWidth[column2.Ordinal] + 1) * 256);
                    }
                    rowIndex = 1;
                }
                XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow;
                foreach (DataColumn column in dtSource.Columns)
                {
                    XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell;
                    string drValue = row[column].ToString();
                    switch (column.DataType.ToString())
                    {
                        case "System.String":
                            {
                                if (isNumeric(drValue, out var result))
                                {
                                    double.TryParse(drValue, out result);
                                    newCell.SetCellValue(result);
                                }
                                else
                                {
                                    newCell.SetCellValue(drValue);
                                }
                                break;
                            }
                        case "System.DateTime":
                            {
                                DateTime.TryParse(drValue, out var dateV);
                                newCell.SetCellValue(dateV);
                                newCell.CellStyle = dateStyle;
                                break;
                            }
                        case "System.Boolean":
                            {
                                bool boolV = false;
                                bool.TryParse(drValue, out boolV);
                                newCell.SetCellValue(boolV);
                                break;
                            }
                        case "System.Int16":
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            {
                                int intV = 0;
                                int.TryParse(drValue, out intV);
                                newCell.SetCellValue(intV);
                                break;
                            }
                        case "System.Double":
                        case "System.Decimal":
                            {
                                double doubV = 0.0;
                                double.TryParse(drValue, out doubV);
                                newCell.SetCellValue(doubV);
                                break;
                            }
                        case "System.DBNull":
                            newCell.SetCellValue("");
                            break;
                        default:
                            newCell.SetCellValue("");
                            break;
                    }
                }
                rowIndex++;
            }
            workbook.Write(fs);
            fs.Close();
        }

        public static void ExportDTtoExcel(DataTable dtSource, string strHeaderText, string strFileName)
        {
            string[] temp = strFileName.Split('.');
            if (temp[temp.Length - 1] == "xls" && dtSource.Columns.Count < 256 && dtSource.Rows.Count < 65536)
            {
                using (MemoryStream ms = ExportDT(dtSource, strHeaderText))
                {
                    using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                    {
                        byte[] data = ms.ToArray();
                        fs.Write(data, 0, data.Length);
                        fs.Flush();
                        return;
                    }
                }
            }
            if (temp[temp.Length - 1] == "xls")
            {
                strFileName += "x";
            }
            using (FileStream fs2 = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
            {
                ExportDTI(dtSource, strHeaderText, fs2);
            }
        }

        public static DataTable ImportExceltoDt(string strFileName)
        {
            if (Path.GetExtension(strFileName) == ".csv" || Path.GetExtension(strFileName) == ".CSV")
            {
                return OpenCSV(strFileName);
            }
            DataTable dt = new DataTable();
            IWorkbook wb;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                wb = WorkbookFactory.Create((Stream)file);
            }
            ISheet sheet = wb.GetSheetAt(0);
            return ImportDt(sheet, 0, needHeader: true);
        }

        public static DataSet ImportExceltoDs(string strFileName)
        {
            string filename = Path.GetFileNameWithoutExtension(strFileName);
            DataSet ds = new DataSet(filename);
            if (Path.GetExtension(strFileName) == ".csv")
            {
                ds.Tables.Add(OpenCSV(strFileName));
                return ds;
            }
            DataTable dt = new DataTable();
            IWorkbook wb;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                wb = WorkbookFactory.Create((Stream)file);
            }
            int number = wb.NumberOfSheets;
            for (int i = 0; i < number; i++)
            {
                string name = wb.GetSheetName(i);
                ISheet sheet = wb.GetSheet(name);
                dt = ImportDt(sheet, 0, needHeader: true);
                ds.Tables.Add(dt);
            }
            return ds;
        }

        public static DataTable ImportExceltoDt(Stream stream)
        {
            try
            {
                DataTable dt = new DataTable();
                IWorkbook wb;
                using (stream)
                {
                    wb = WorkbookFactory.Create(stream);
                }
                ISheet sheet = wb.GetSheetAt(0);
                return ImportDt(sheet, 0, needHeader: true);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public static DataTable ImportExceltoDt(Stream stream, string sheetName, int HeaderRowIndex)
        {
            try
            {
                DataTable dt = new DataTable();
                IWorkbook wb;
                using (stream)
                {
                    wb = WorkbookFactory.Create(stream);
                }
                ISheet sheet = wb.GetSheet(sheetName);
                return ImportDt(sheet, HeaderRowIndex, needHeader: true);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public static DataSet ImportExceltoDs(Stream stream)
        {
            try
            {
                DataSet ds = new DataSet();
                IWorkbook wb;
                using (stream)
                {
                    wb = WorkbookFactory.Create(stream);
                }
                for (int i = 0; i < wb.NumberOfSheets; i++)
                {
                    DataTable dt = new DataTable();
                    ISheet sheet = wb.GetSheetAt(i);
                    dt = ImportDt(sheet, 0, needHeader: true);
                    ds.Tables.Add(dt);
                }
                return ds;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public static DataSet ImportExceltoDs(Stream stream, Dictionary<string, int> dict)
        {
            try
            {
                DataSet ds = new DataSet();
                IWorkbook wb;
                using (stream)
                {
                    wb = WorkbookFactory.Create(stream);
                }
                foreach (string key in dict.Keys)
                {
                    DataTable dt = new DataTable();
                    ISheet sheet = wb.GetSheet(key);
                    dt = ImportDt(sheet, dict[key], needHeader: true);
                    ds.Tables.Add(dt);
                }
                return ds;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public static DataTable ImportExceltoDt(string strFileName, string SheetName, int HeaderRowIndex)
        {
            IWorkbook wb;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                wb = new HSSFWorkbook(file);
            }
            ISheet sheet = wb.GetSheet(SheetName);
            DataTable table = new DataTable();
            table = ImportDt(sheet, HeaderRowIndex, needHeader: true);
            HSSFWorkbook workbook = null;
            sheet = null;
            return table;
        }

        public static DataTable ImportExceltoDt(string strFileName, int SheetIndex, int HeaderRowIndex)
        {
            IWorkbook wb;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                wb = WorkbookFactory.Create((Stream)file);
            }
            ISheet isheet = wb.GetSheetAt(SheetIndex);
            DataTable table = new DataTable();
            table = ImportDt(isheet, HeaderRowIndex, needHeader: true);
            HSSFWorkbook workbook = null;
            isheet = null;
            return table;
        }

        public static DataTable ImportExceltoDt(string strFileName, string SheetName, int HeaderRowIndex, bool needHeader)
        {
            IWorkbook wb;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                wb = WorkbookFactory.Create((Stream)file);
            }
            ISheet sheet = wb.GetSheet(SheetName);
            DataTable table = new DataTable();
            table = ImportDt(sheet, HeaderRowIndex, needHeader);
            HSSFWorkbook workbook = null;
            sheet = null;
            return table;
        }

        public static DataTable ImportExceltoDt(string strFileName, int SheetIndex, int HeaderRowIndex, bool needHeader)
        {
            IWorkbook wb;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                wb = WorkbookFactory.Create((Stream)file);
            }
            ISheet sheet = wb.GetSheetAt(SheetIndex);
            DataTable table = new DataTable();
            table = ImportDt(sheet, HeaderRowIndex, needHeader);
            HSSFWorkbook workbook = null;
            sheet = null;
            return table;
        }

        private static DataTable ImportDt(ISheet sheet, int HeaderRowIndex, bool needHeader)
        {
            DataTable table = new DataTable(sheet.SheetName);
            try
            {
                int cellCount;
                if (HeaderRowIndex < 0 || !needHeader)
                {
                    IRow headerRow = sheet.GetRow(0);
                    cellCount = headerRow.LastCellNum;
                    for (int k = headerRow.FirstCellNum; k <= cellCount; k++)
                    {
                        DataColumn column2 = new DataColumn(Convert.ToString(k));
                        table.Columns.Add(column2);
                    }
                }
                else
                {
                    IRow headerRow = sheet.GetRow(HeaderRowIndex);
                    cellCount = headerRow.LastCellNum;
                    for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
                    {
                        if (headerRow.GetCell(i) == null)
                        {
                            if (table.Columns.IndexOf(Convert.ToString(i)) > 0)
                            {
                                DataColumn column3 = new DataColumn(Convert.ToString("重复列名" + i));
                                table.Columns.Add(column3);
                            }
                            else
                            {
                                DataColumn column4 = new DataColumn(Convert.ToString(i));
                                table.Columns.Add(column4);
                            }
                        }
                        else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0)
                        {
                            DataColumn column5 = new DataColumn(Convert.ToString("重复列名" + i));
                            table.Columns.Add(column5);
                        }
                        else
                        {
                            DataColumn column = new DataColumn(headerRow.GetCell(i).ToString());
                            table.Columns.Add(column);
                        }
                    }
                }
                int rowCount = sheet.LastRowNum;
                for (int j = HeaderRowIndex + 1; j <= sheet.LastRowNum; j++)
                {
                    try
                    {
                        IRow row = ((sheet.GetRow(j) != null) ? sheet.GetRow(j) : sheet.CreateRow(j));
                        DataRow dataRow = table.NewRow();
                        for (int l = row.FirstCellNum; l <= cellCount; l++)
                        {
                            try
                            {
                                if (row.GetCell(l) == null)
                                {
                                    continue;
                                }
                                switch (row.GetCell(l).CellType)
                                {
                                    case CellType.String:
                                        {
                                            string str = row.GetCell(l).StringCellValue;
                                            if (str != null && str.Length > 0)
                                            {
                                                dataRow[l] = str.ToString();
                                            }
                                            else
                                            {
                                                dataRow[l] = null;
                                            }
                                            break;
                                        }
                                    case CellType.Numeric:
                                        {
                                            if (DateTime.TryParse(row.GetCell(l).ToString(), out var _))
                                            {
                                                dataRow[l] = row.GetCell(l).NumericCellValue;
                                            }
                                            else
                                            {
                                                dataRow[l] = Convert.ToDouble(row.GetCell(l).NumericCellValue);
                                            }
                                            break;
                                        }
                                    case CellType.Boolean:
                                        dataRow[l] = Convert.ToString(row.GetCell(l).BooleanCellValue);
                                        break;
                                    case CellType.Error:
                                        dataRow[l] = ErrorEval.GetText(row.GetCell(l).ErrorCellValue);
                                        break;
                                    case CellType.Formula:
                                        switch (row.GetCell(l).CachedFormulaResultType)
                                        {
                                            case CellType.String:
                                                {
                                                    string strFORMULA = row.GetCell(l).StringCellValue;
                                                    if (strFORMULA != null && strFORMULA.Length > 0)
                                                    {
                                                        dataRow[l] = strFORMULA.ToString();
                                                    }
                                                    else
                                                    {
                                                        dataRow[l] = null;
                                                    }
                                                    break;
                                                }
                                            case CellType.Numeric:
                                                dataRow[l] = Convert.ToString(row.GetCell(l).NumericCellValue);
                                                break;
                                            case CellType.Boolean:
                                                dataRow[l] = Convert.ToString(row.GetCell(l).BooleanCellValue);
                                                break;
                                            case CellType.Error:
                                                dataRow[l] = ErrorEval.GetText(row.GetCell(l).ErrorCellValue);
                                                break;
                                            default:
                                                dataRow[l] = "";
                                                break;
                                        }
                                        break;
                                    default:
                                        dataRow[l] = "";
                                        break;
                                }
                            }
                            catch (Exception exception3)
                            {
                                //LogHelper.Error(exception3.ToString());
                            }
                        }
                        table.Rows.Add(dataRow);
                    }
                    catch (Exception exception2)
                    {
                        //LogHelper.Error(exception2.ToString());
                    }
                }
            }
            catch (Exception exception)
            {
                //LogHelper.Error(exception.ToString());
            }
            return table;
        }

        public static void InsertSheet(string outputFile, string sheetname, DataTable dt)
        {
            FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
            IWorkbook hssfworkbook = WorkbookFactory.Create((Stream)readfile);
            int num = hssfworkbook.GetSheetIndex(sheetname);
            ISheet sheet1 = ((num < 0) ? hssfworkbook.CreateSheet(sheetname) : hssfworkbook.GetSheet(sheetname));
            try
            {
                if (sheet1.GetRow(0) == null)
                {
                    sheet1.CreateRow(0);
                }
                for (int coluid2 = 0; coluid2 < dt.Columns.Count; coluid2++)
                {
                    if (sheet1.GetRow(0).GetCell(coluid2) == null)
                    {
                        sheet1.GetRow(0).CreateCell(coluid2);
                    }
                    sheet1.GetRow(0).GetCell(coluid2).SetCellValue(dt.Columns[coluid2].ColumnName);
                }
            }
            catch (Exception ex3)
            {
                //LogHelper.Error(ex3.ToString());
                throw;
            }
            for (int i = 1; i <= dt.Rows.Count; i++)
            {
                try
                {
                    if (sheet1.GetRow(i) == null)
                    {
                        sheet1.CreateRow(i);
                    }
                    for (int coluid = 0; coluid < dt.Columns.Count; coluid++)
                    {
                        if (sheet1.GetRow(i).GetCell(coluid) == null)
                        {
                            sheet1.GetRow(i).CreateCell(coluid);
                        }
                        sheet1.GetRow(i).GetCell(coluid).SetCellValue(dt.Rows[i - 1][coluid].ToString());
                    }
                }
                catch (Exception ex2)
                {
                    //LogHelper.Error(ex2.ToString());
                }
            }
            try
            {
                readfile.Close();
                FileStream writefile = new FileStream(outputFile, FileMode.OpenOrCreate, FileAccess.Write);
                hssfworkbook.Write(writefile);
                writefile.Close();
            }
            catch (Exception ex)
            {
                //LogHelper.Error(ex.ToString());
            }
        }

        public static void UpdateExcel(string outputFile, string sheetname, string[] updateData, int coluid, int rowid)
        {
            IWorkbook hssfworkbook = null;
            ISheet sheet1 = hssfworkbook.GetSheet(sheetname);
            for (int i = 0; i < updateData.Length; i++)
            {
                try
                {
                    if (sheet1.GetRow(i + rowid) == null)
                    {
                        sheet1.CreateRow(i + rowid);
                    }
                    if (sheet1.GetRow(i + rowid).GetCell(coluid) == null)
                    {
                        sheet1.GetRow(i + rowid).CreateCell(coluid);
                    }
                    sheet1.GetRow(i + rowid).GetCell(coluid).SetCellValue(updateData[i]);
                }
                catch (Exception ex2)
                {
                    //LogHelper.Error(ex2.ToString());
                    throw;
                }
            }
            try
            {
                FileStream writefile = new FileStream(outputFile, FileMode.OpenOrCreate, FileAccess.Write);
                hssfworkbook.Write(writefile);
                writefile.Close();
            }
            catch (Exception ex)
            {
                //LogHelper.Error(ex.ToString());
            }
        }

        public static void UpdateExcel(string outputFile, string sheetname, string[][] updateData, int[] coluids, int rowid)
        {
            FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
            HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
            readfile.Close();
            ISheet sheet1 = hssfworkbook.GetSheet(sheetname);
            for (int j = 0; j < coluids.Length; j++)
            {
                for (int i = 0; i < updateData[j].Length; i++)
                {
                    try
                    {
                        if (sheet1.GetRow(i + rowid) == null)
                        {
                            sheet1.CreateRow(i + rowid);
                        }
                        if (sheet1.GetRow(i + rowid).GetCell(coluids[j]) == null)
                        {
                            sheet1.GetRow(i + rowid).CreateCell(coluids[j]);
                        }
                        sheet1.GetRow(i + rowid).GetCell(coluids[j]).SetCellValue(updateData[j][i]);
                    }
                    catch (Exception ex2)
                    {
                        //LogHelper.Error(ex2.ToString());
                    }
                }
            }
            try
            {
                FileStream writefile = new FileStream(outputFile, FileMode.Create);
                hssfworkbook.Write(writefile);
                writefile.Close();
            }
            catch (Exception ex)
            {
                //LogHelper.Error(ex.ToString());
            }
        }

        public static void UpdateExcel(string outputFile, string sheetname, double[] updateData, int coluid, int rowid)
        {
            FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
            HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
            ISheet sheet1 = hssfworkbook.GetSheet(sheetname);
            for (int i = 0; i < updateData.Length; i++)
            {
                try
                {
                    if (sheet1.GetRow(i + rowid) == null)
                    {
                        sheet1.CreateRow(i + rowid);
                    }
                    if (sheet1.GetRow(i + rowid).GetCell(coluid) == null)
                    {
                        sheet1.GetRow(i + rowid).CreateCell(coluid);
                    }
                    sheet1.GetRow(i + rowid).GetCell(coluid).SetCellValue(updateData[i]);
                }
                catch (Exception ex2)
                {
                    //LogHelper.Error(ex2.ToString());
                    throw;
                }
            }
            try
            {
                readfile.Close();
                FileStream writefile = new FileStream(outputFile, FileMode.Create, FileAccess.Write);
                hssfworkbook.Write(writefile);
                writefile.Close();
            }
            catch (Exception ex)
            {
                //LogHelper.Error(ex.ToString());
            }
        }

        public static void UpdateExcel(string outputFile, string sheetname, double[][] updateData, int[] coluids, int rowid)
        {
            FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
            HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
            readfile.Close();
            ISheet sheet1 = hssfworkbook.GetSheet(sheetname);
            for (int j = 0; j < coluids.Length; j++)
            {
                for (int i = 0; i < updateData[j].Length; i++)
                {
                    try
                    {
                        if (sheet1.GetRow(i + rowid) == null)
                        {
                            sheet1.CreateRow(i + rowid);
                        }
                        if (sheet1.GetRow(i + rowid).GetCell(coluids[j]) == null)
                        {
                            sheet1.GetRow(i + rowid).CreateCell(coluids[j]);
                        }
                        sheet1.GetRow(i + rowid).GetCell(coluids[j]).SetCellValue(updateData[j][i]);
                    }
                    catch (Exception ex2)
                    {
                        //LogHelper.Error(ex2.ToString());
                    }
                }
            }
            try
            {
                FileStream writefile = new FileStream(outputFile, FileMode.Create);
                hssfworkbook.Write(writefile);
                writefile.Close();
            }
            catch (Exception ex)
            {
                //LogHelper.Error(ex.ToString());
            }
        }

        public static int GetSheetNumber(string outputFile)
        {
            int number = 0;
            try
            {
                FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
                HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
                number = hssfworkbook.NumberOfSheets;
            }
            catch (Exception exception)
            {
                //LogHelper.Error(exception.ToString());
            }
            return number;
        }

        public static ArrayList GetSheetName(string outputFile)
        {
            ArrayList arrayList = new ArrayList();
            try
            {
                FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
                HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
                for (int i = 0; i < hssfworkbook.NumberOfSheets; i++)
                {
                    arrayList.Add(hssfworkbook.GetSheetName(i));
                }
            }
            catch (Exception exception)
            {
                //LogHelper.Error(exception.ToString());
            }
            return arrayList;
        }

        public static bool isNumeric(string message, out double result)
        {
            Regex rex = new Regex("^[-]?\\d+[.]?\\d*$");
            result = -1.0;
            if (rex.IsMatch(message))
            {
                result = double.Parse(message);
                return true;
            }
            return false;
        }

        public static MemoryStream Export(DataTable dtSource, string strHeaderText)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "NPOI";
            workbook.DocumentSummaryInformation = dsi;
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Author = "文件作者信息";
            si.ApplicationName = "创建程序信息";
            si.LastAuthor = "最后保存者信息";
            si.Comments = "作者信息";
            si.Title = "标题信息";
            si.Subject = "主题信息";
            si.CreateDateTime = DateTime.Now;
            workbook.SummaryInformation = si;
            HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
            HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
            int[] arrColWidth = new int[dtSource.Columns.Count];
            foreach (DataColumn item in dtSource.Columns)
            {
                arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
            }
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }
            int rowIndex = 0;
            foreach (DataRow row in dtSource.Rows)
            {
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet() as HSSFSheet;
                    }
                    if (string.IsNullOrEmpty(strHeaderText))
                    {
                        HSSFRow headerRow2 = sheet.CreateRow(0) as HSSFRow;
                        headerRow2.HeightInPoints = 25f;
                        headerRow2.CreateCell(0).SetCellValue(strHeaderText);
                        HSSFCellStyle headStyle2 = workbook.CreateCellStyle() as HSSFCellStyle;
                        HSSFFont font2 = workbook.CreateFont() as HSSFFont;
                        font2.FontHeightInPoints = 20;
                        font2.Boldweight = 700;
                        headStyle2.SetFont(font2);
                        headerRow2.GetCell(0).CellStyle = headStyle2;
                        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
                    }
                    HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
                    HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
                    HSSFFont font = workbook.CreateFont() as HSSFFont;
                    font.FontHeightInPoints = 10;
                    font.Boldweight = 700;
                    headStyle.SetFont(font);
                    foreach (DataColumn column2 in dtSource.Columns)
                    {
                        headerRow.CreateCell(column2.Ordinal).SetCellValue(column2.ColumnName);
                        headerRow.GetCell(column2.Ordinal).CellStyle = headStyle;
                        sheet.SetColumnWidth(column2.Ordinal, (arrColWidth[column2.Ordinal] + 1) * 256);
                    }
                    rowIndex = 1;
                }
                HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
                foreach (DataColumn column in dtSource.Columns)
                {
                    HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;
                    string drValue = row[column].ToString();
                    switch (column.DataType.ToString())
                    {
                        case "System.String":
                            newCell.SetCellValue(drValue);
                            break;
                        case "System.DateTime":
                            {
                                DateTime.TryParse(drValue, out var dateV);
                                newCell.SetCellValue(dateV);
                                newCell.CellStyle = dateStyle;
                                break;
                            }
                        case "System.Boolean":
                            {
                                bool boolV = false;
                                bool.TryParse(drValue, out boolV);
                                newCell.SetCellValue(boolV);
                                break;
                            }
                        case "System.Int16":
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            {
                                int intV = 0;
                                int.TryParse(drValue, out intV);
                                newCell.SetCellValue(intV);
                                break;
                            }
                        case "System.Double":
                        case "System.Decimal":
                            {
                                double doubV = 0.0;
                                double.TryParse(drValue, out doubV);
                                newCell.SetCellValue(doubV);
                                break;
                            }
                        case "System.DBNull":
                            newCell.SetCellValue("");
                            break;
                        default:
                            newCell.SetCellValue("");
                            break;
                    }
                }
                rowIndex++;
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0L;
                return ms;
            }
        }

        private static MemoryStream ExportDataSetToExcel(DataSet sourceDs, string sheetName)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            MemoryStream ms = new MemoryStream();
            string[] sheetNames = sheetName.Split(',');
            for (int i = 0; i < sheetNames.Length; i++)
            {
                ISheet sheet = workbook.CreateSheet(sheetNames[i]);
                IRow headerRow = sheet.CreateRow(0);
                HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
                HSSFFont font = workbook.CreateFont() as HSSFFont;
                font.FontHeightInPoints = 10;
                font.Boldweight = 700;
                headStyle.SetFont(font);
                int[] arrColWidth = new int[sourceDs.Tables[i].Columns.Count];
                foreach (DataColumn item in sourceDs.Tables[i].Columns)
                {
                    arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
                }
                foreach (DataColumn column2 in sourceDs.Tables[i].Columns)
                {
                    headerRow.CreateCell(column2.Ordinal).SetCellValue(column2.ColumnName);
                    headerRow.GetCell(column2.Ordinal).CellStyle = headStyle;
                    sheet.SetColumnWidth(column2.Ordinal, (arrColWidth[column2.Ordinal] + 1) * 256);
                }
                int rowIndex = 1;
                foreach (DataRow row in sourceDs.Tables[i].Rows)
                {
                    IRow dataRow = sheet.CreateRow(rowIndex);
                    foreach (DataColumn column in sourceDs.Tables[i].Columns)
                    {
                        dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                    }
                    rowIndex++;
                }
            }
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0L;
            workbook = null;
            return ms;
        }

        public static bool HasData(Stream excelFileStream)
        {
            using (excelFileStream)
            {
                IWorkbook workBook = new HSSFWorkbook(excelFileStream);
                if (workBook.NumberOfSheets > 0)
                {
                    ISheet sheet = workBook.GetSheetAt(0);
                    return sheet.PhysicalNumberOfRows > 0;
                }
            }
            return false;
        }

        public static void ExportListToExcel<T>(string fileName, string sheetName, IList<T> dataList)
        {
            MemoryStream ms = new MemoryStream();
            IWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(sheetName);
            IRow headerRow = sheet.CreateRow(0);
            ICellStyle headerCellStyle = workbook.CreateCellStyle();
            ICellStyle textCellStyle = workbook.CreateCellStyle();
            ICellStyle numberCellStyle = workbook.CreateCellStyle();
            IFont headerfont = workbook.CreateFont();
            IFont font = workbook.CreateFont();
            headerfont.FontHeightInPoints = 10;
            headerfont.FontName = "微软雅黑";
            font.FontHeightInPoints = 10;
            font.FontName = "微软雅黑";
            headerCellStyle.Alignment = HorizontalAlignment.Left;
            headerCellStyle.VerticalAlignment = VerticalAlignment.Center;
            headerCellStyle.WrapText = true;
            textCellStyle.Alignment = HorizontalAlignment.Left;
            textCellStyle.VerticalAlignment = VerticalAlignment.Center;
            textCellStyle.WrapText = true;
            numberCellStyle.Alignment = HorizontalAlignment.Right;
            numberCellStyle.VerticalAlignment = VerticalAlignment.Center;
            numberCellStyle.WrapText = true;
            headerCellStyle.SetFont(headerfont);
            textCellStyle.SetFont(font);
            numberCellStyle.SetFont(font);
            Type parentType = typeof(T);
            PropertyInfo[] properties = parentType.GetProperties();
            int index = 0;
            PropertyInfo[] array = properties;
            foreach (PropertyInfo propertie2 in array)
            {
                string displayName = "";
                List<Attribute> attributes2 = propertie2.GetCustomAttributes().ToList();
                foreach (Attribute attribute in attributes2)
                {
                    if (attribute is DisplayNameAttribute)
                    {
                        DisplayNameAttribute displayNameAttribute = attribute as DisplayNameAttribute;
                        displayName = displayNameAttribute.DisplayName;
                    }
                }
                ICell cell2 = headerRow.CreateCell(index);
                cell2.CellStyle = headerCellStyle;
                cell2.SetCellValue(displayName);
                index++;
            }
            int rowIndex = 1;
            foreach (T data in dataList)
            {
                index = 0;
                IRow dataRow = sheet.CreateRow(rowIndex);
                PropertyInfo[] array2 = properties;
                foreach (PropertyInfo propertie in array2)
                {
                    List<Attribute> attributes = propertie.GetCustomAttributes().ToList();
                    if (attributes.Count((Attribute x) => x is DisplayNameAttribute) != 0)
                    {
                        string value = "";
                        object obj = propertie.GetValue(data, null);
                        if (obj != null)
                        {
                            value = obj.ToString();
                        }
                        ICell cell = dataRow.CreateCell(index);
                        int width = (int)sheet.GetColumnWidth(cell.ColumnIndex);
                        int widthNew = value.Length * 500;
                        if (widthNew < 4000)
                        {
                            widthNew = 4000;
                        }
                        if (widthNew > width)
                        {
                            sheet.SetColumnWidth(cell.ColumnIndex, widthNew);
                        }
                        if (propertie.PropertyType == typeof(int) || propertie.PropertyType == typeof(float) || propertie.PropertyType == typeof(double) || propertie.PropertyType == typeof(decimal))
                        {
                            cell.CellStyle = numberCellStyle;
                            cell.SetCellValue(double.Parse(value));
                        }
                        else
                        {
                            cell.CellStyle = textCellStyle;
                            cell.SetCellValue(value.ToString());
                        }
                        index++;
                    }
                }
                rowIndex++;
            }
            workbook.Write(ms);
            FileStream dumpFile = new FileStream(fileName, FileMode.Create, FileAccess.ReadWrite);
            ms.WriteTo(dumpFile);
            ms.Flush();
            ms.Position = 0L;
            dumpFile.Close();
        }

        public static DataTable OpenCSV(string filePath)
        {
            Encoding encoding = GetType(filePath);
            DataTable dt = new DataTable();
            FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
            StreamReader sr = new StreamReader(fs, encoding);
            string strLine = "";
            string[] aryLine = null;
            string[] tableHead = null;
            int columnCount = 0;
            bool IsFirst = true;
            while ((strLine = sr.ReadLine()) != null)
            {
                if (IsFirst)
                {
                    tableHead = strLine.Split(',');
                    IsFirst = false;
                    columnCount = tableHead.Length;
                    for (int i = 0; i < columnCount; i++)
                    {
                        DataColumn dc = new DataColumn(tableHead[i]);
                        dt.Columns.Add(dc);
                    }
                }
                else
                {
                    aryLine = strLine.Split(',');
                    DataRow dr = dt.NewRow();
                    for (int j = 0; j < columnCount; j++)
                    {
                        dr[j] = aryLine[j];
                    }
                    dt.Rows.Add(dr);
                }
            }
            if (aryLine != null && aryLine.Length != 0)
            {
                dt.DefaultView.Sort = tableHead[0] + " asc";
            }
            sr.Close();
            fs.Close();
            return dt;
        }

        public static Encoding GetType(string FILE_NAME)
        {
            FileStream fs = new FileStream(FILE_NAME, FileMode.Open, FileAccess.Read);
            Encoding r = GetType(fs);
            fs.Close();
            return r;
        }

        public static Encoding GetType(FileStream fs)
        {
            byte[] Unicode = new byte[3] { 255, 254, 65 };
            byte[] UnicodeBIG = new byte[3] { 254, 255, 0 };
            byte[] UTF8 = new byte[3] { 239, 187, 191 };
            Encoding reVal = Encoding.Default;
            BinaryReader r = new BinaryReader(fs, Encoding.Default);
            int.TryParse(fs.Length.ToString(), out var i);
            byte[] ss = r.ReadBytes(i);
            if (IsUTF8Bytes(ss) || (ss[0] == 239 && ss[1] == 187 && ss[2] == 191))
            {
                reVal = Encoding.UTF8;
            }
            else if (ss[0] == 254 && ss[1] == byte.MaxValue && ss[2] == 0)
            {
                reVal = Encoding.BigEndianUnicode;
            }
            else if (ss[0] == byte.MaxValue && ss[1] == 254 && ss[2] == 65)
            {
                reVal = Encoding.Unicode;
            }
            r.Close();
            return reVal;
        }

        private static bool IsUTF8Bytes(byte[] data)
        {
            int charByteCounter = 1;
            for (int i = 0; i < data.Length; i++)
            {
                byte curByte = data[i];
                if (charByteCounter == 1)
                {
                    if (curByte >= 128)
                    {
                        while (((curByte <<= 1) & 0x80u) != 0)
                        {
                            charByteCounter++;
                        }
                        if (charByteCounter == 1 || charByteCounter > 6)
                        {
                            return false;
                        }
                    }
                }
                else
                {
                    if ((curByte & 0xC0) != 128)
                    {
                        return false;
                    }
                    charByteCounter--;
                }
            }
            if (charByteCounter > 1)
            {
                throw new Exception("非预期的byte格式");
            }
            return true;
        }


        public static List<T> ImportExcelToModelList<T>(string strFileName) where T : new()
        {
            DataTable dt = ImportExceltoDt(strFileName);
            return DataTableToList<T>(dt);
        }
        public static List<T> DataTableToList<T>(DataTable dt) where T : new()
        {
            var list = new List<T>();
            var props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

            foreach (DataRow row in dt.Rows)
            {
                var item = new T();

                foreach (var prop in props)
                {
                    // 假设DataTable的列名和Model的属性名相同  
                    if (dt.Columns.Contains(prop.Name))
                    {
                        // 尝试将DataRow中的值转换为属性类型  
                        try
                        {
                            // 使用Convert.ChangeType来处理类型转换  
                            object value = row[prop.Name];
                            if (value != DBNull.Value)
                            {
                                value = Convert.ChangeType(value, prop.PropertyType);
                                prop.SetValue(item, value, null);
                            }
                        }
                        catch (Exception ex)
                        {
                            // 处理或记录类型转换失败的情况  
                            Console.WriteLine($"无法将值转换为 {prop.PropertyType.Name} 类型: {ex.Message}");
                        }
                    }
                }

                list.Add(item);
            }

            return list;
        }




    }


}
